package com.midian.common.dao;

import java.lang.reflect.Method;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.Transaction;
import org.hibernate.transform.Transformers;
import org.hibernate.type.StandardBasicTypes;
import org.hibernatespatial.GeometryUserType;

import com.midian.common.exception.ModelException;
import com.midian.common.pager.Pager;
import com.midian.model.common.BaseModel;
import com.midian.model.common.Model;
import com.vividsolutions.jts.geom.Point;

/**
 * 
 * @Title:工具类  
 * @Desription:SQL查询类
 * @Company:MDKG
 * @ClassName:BaseDaoBySql.java
 * @Author:Mengfh
 * @CreateDate:2013-6-7 下午5:18:32  
 * @UpdateUser:Mengfh  
 * @Version:0.1
 */
public class BaseDaoBySql<T extends Model> extends BaseDaoTemplet<T> {
	/***
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: TODO
	 * @Author:BEN
	 * @CreateDate:2013-9-17 上午10:15:43
	 * @param listData 数据列
	 * @param propertyName 属性名称
	 * @param myClass 类
	 * @return
	 * @throws Exception
	 */
	public <E> List<E> reflectDate(List<Object[]> listData ,List<String> propertyName, Class<E> myClass) throws Exception {
		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		List<E> returnList = new ArrayList<E>();

		for (Object[] dataArray : listData) {
			E entity = myClass.newInstance();
			
			for (int i = 0; i < propertyName.size(); i++) {
				String name = propertyName.get(i);
				Object value =dataArray[i];
				
				if(value==null){
					continue;
				}
				
				String getmethodName = "get"+name.substring(0,1).toUpperCase()+name.substring(1);
				Method getMethod=entity.getClass().getMethod(getmethodName);
				Class<?> paramType = getMethod.getReturnType();
				////System.out.println(" 方法名称="+ getmethodName);
				String setmethodName = "set"+name.substring(0,1).toUpperCase()+name.substring(1);
				Method setMethod =	entity.getClass().getMethod(setmethodName,paramType);
				try {
				 if(paramType.equals(Integer.class)){
						 
						setMethod.invoke(entity,Integer.parseInt(value.toString()));
					} else if(paramType.equals(Double.class)) {
					 
						setMethod.invoke(entity, Double.parseDouble(value.toString()));
					} else if(paramType.equals(Float.class)) {
					 
						setMethod.invoke(entity, Float.parseFloat(value.toString()));
					} else if(paramType.equals(Date.class)) {
				 
						setMethod.invoke(entity,sdf.parse(value.toString()));
					} else if(paramType.equals(Boolean.class)) {
					 
						setMethod.invoke(entity,Boolean.parseBoolean(value.toString()));
					} else if(paramType.equals(String.class)) {
						 
						setMethod.invoke(entity, value.toString());
					}
				} catch (Exception e) {
					//System.out.println(getmethodName + " " + name);
				}
				// //System.out.println(getMethod.invoke(entity) +" 方法名称="+ getmethodName);
		}
		  returnList.add(entity);
	}
	return returnList;
}
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL获取集合
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:18:55
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @return
	 * @throws ModelException
	 */
	public List pageFindBySql(String sql ,Object[] values) throws ModelException {
		return pageFindBySql(sql, values, null, null, null);
	}
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL分页获取集合
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:18:50
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @param pager 分页对象
	 * @return
	 * @throws ModelException
	 */
	public List pageFindBySql(String sql ,Object[] values, Pager pager) throws ModelException {
		return pageFindBySql(sql, values, pager, null, null);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL分页获取集合
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:20:03
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @param startRow 开始行
	 * @param pageSize 每页行数
	 * @return
	 * @throws ModelException
	 */
	public List pageFindBySql(String sql ,Object[] values,int startRow, int pageSize) throws ModelException {
		Pager pager = new Pager();
		pager.setStartRow(startRow);
		pager.setPageSize(pageSize);
		return pageFindBySql(sql, values, pager, null, null);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL分页获取集合
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:20:37
	 * @param sql SQL语句
	 * @param startRow 开始行
	 * @param pageSize 每页行数
	 * @return
	 * @throws ModelException
	 */
	public List pageFindBySql(String sql ,int startRow, int pageSize) throws ModelException {
		Pager pager = new Pager();
		pager.setStartRow(startRow);
		pager.setPageSize(pageSize);
		return pageFindBySql(sql, null, pager, null, null);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL获取集合
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:21:08
	 * @param sql SQL语句
	 * @return
	 * @throws ModelException
	 */
	public List pageFindBySql(String sql) throws ModelException {
		return pageFindBySql(sql, null, null, null, null);
	}
	
	/**
	 * 获取当前session对象
	 * @return
	 */
	public Session getCurrSession() {
		return super.getSession();
	}
	
	/**
	 * 指定session对象，执行SQL语句
	 * @param sql SQL语句
	 * @param values 值集合
	 * @param session 指定的session对象
	 * @return
	 * @throws ModelException
	 */
	public int updateSqlByDefSession(String sql, Object[] values, Session session) throws ModelException {
		if(session != null) {
			Transaction tc = null;
			try {
				 SQLQuery sqlQuery = session.createSQLQuery(sql);
				 setParamList(sqlQuery, values);
				 tc = session.beginTransaction(); //打开事务
				 int result = sqlQuery.executeUpdate();
				 tc.commit(); //提交事件
				 return result;
			} catch (Exception e) {
				if(tc != null) {
					tc.rollback(); //出现异常，回滚事务
				}
				throw new ModelException(sql + "执行失败");
			}
		}
		return 0;
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL进行更新
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:21:24
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @throws ModelException
	 */
	public int updateSQL(String sql,Object[] values) throws ModelException {
		try{
			 Session session =super.getSession();
			 SQLQuery sqlQuery = session.createSQLQuery(sql);
			 setParamList(sqlQuery, values);
			 return sqlQuery.executeUpdate();
		}catch(Exception e){
			//e.printStackTrace();
			throw new ModelException(sql+"执行失败");
		}
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL更新
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:21:46
	 * @param sql SQL语句
	 * @throws ModelException
	 */
	public int updateSQL(String sql )throws ModelException{
		return updateSQL( sql,null);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL获取集合(带分页)，并获取传入的Pager对象
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:22:01
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @param pager 分页对象
	 * @return
	 * @throws ModelException
	 */
	public Map<String, Object> pageFindBySqlWithPager(String sql, Object[] values, Pager pager) throws ModelException {
		return pageFindBySqlAndGetPager(sql, values, pager, null, null);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL获取集合(带分页)(通过类，封装对象)，并获取传入的Pager对象
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:22:51
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @param pager 分页对象
	 * @param targetClass 查询后需转对象的类名
	 * @return
	 * @throws ModelException
	 */
	public Map<String, Object> pageFindBySql(String sql, Object[] values, Pager pager, Class targetClass) throws ModelException {
		return pageFindBySqlAndGetPager(sql, values, pager, null, targetClass);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL查询集合(带分页)(通过别名，封装对象)
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:23:40
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @param pager 分页对象
	 * @param extraName 查询别名
	 * @param targetClass 查询后需转对象的类名
	 * @return
	 * @throws ModelException
	 */
	public List pageFindBySql(String sql, Object[] values, Pager pager, String extraName, Class targetClass) throws ModelException {
		Session session = super.getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		if(StringUtils.isNotBlank(extraName) && targetClass != null) {
			sqlQuery.addEntity(extraName, targetClass); //根据别名，封装查询结果集的对象
		} else if(targetClass != null) {
			sqlQuery.addEntity(targetClass); //封装查询结果集的对象
		}
		if (pager != null) {
			try {
				pager.setTotalRows(getCountBySql(sql, values));
			} catch (ModelException e) {
				throw new HibernateException("--查询记录总数失败--", e);
			}
			sqlQuery.setFirstResult(pager.getStartRow());
			sqlQuery.setMaxResults(pager.getPageSize());
		}
		setParamList(sqlQuery,values);
		return sqlQuery.list();
	}
	
	/**
	 * 根据SQL语句，生成Hibernate需要类型处理的字段名称列表
	 * @param sql SQL语句
	 * @return
	 */
	private static String[] getFieldNamesBySql(String sql) {
		List<String> fieldNameList = null;
		if(StringUtils.isNotBlank(sql)) {
			sql = sql.replaceAll("\\s+FROM\\s+", " from ").replaceAll("\\s+as\\s+", " ").replaceAll("\\s+AS\\s+", " ");
			String[] details = sql.substring(7, sql.indexOf(" from ")).split("\\,");
			fieldNameList = new ArrayList<String>();
			for (String detail : details) {
				detail = detail.trim();
				int pointIndex = detail.indexOf(".");
				if(pointIndex != -1) {
					detail = detail.substring(pointIndex + 1); //截取别名前缀
				}
				String[] fieldDetails = detail.split(" ");
				String targetFieldName = fieldDetails[fieldDetails.length - 1];
				if(targetFieldName.indexOf("'") != -1) {
					targetFieldName = targetFieldName.substring(1, targetFieldName.length() - 1); //截取属性名中的单引号
				}
				fieldNameList.add(targetFieldName);
			}
		}
		return fieldNameList != null ? fieldNameList.toArray(new String[]{}) : null;
	}
	
	/**
	 * SQL查询
	 * @param sql SQL语句
	 * @param values 值集合
	 * @param pager 分页对象
	 * @param targetClass 目标类
	 * @return
	 * @throws ModelException
	 */
	public <E> List<E> pageFindBySqlWithFieldNames(String sql, Object[] values, Pager pager, 
			Class<E> targetClass) throws ModelException {
		return pageFindBySqlWithFieldNames(sql, values, pager, targetClass, getFieldNamesBySql(sql), false);
	}
	
	/**
	 * SQL查询
	 * @param sql SQL语句
	 * @param values 值集合
	 * @param pager 分页对象
	 * @param targetClass 目标类
	 * @param isPropChain 别名是否为属性链
	 * @return
	 * @throws ModelException
	 */
	public <E> List<E> pageFindBySqlWithFieldNames(String sql, Object[] values, Pager pager, 
			Class<E> targetClass, boolean isPropChain) throws ModelException {
		return pageFindBySqlWithFieldNames(sql, values, pager, targetClass, getFieldNamesBySql(sql), isPropChain);
	}
	
	/**
	 * SQL查询
	 * @param sql SQL语句
	 * @param values 值集合
	 * @param pager 分页对象
	 * @param targetClass 目标类
	 * @param fieldNames 属性集合（用于显式指定属性的类型，为空时不进行设置）
	 * @return
	 * @throws ModelException
	 */
	public <E> List<E> pageFindBySqlWithFieldNames(String sql, Object[] values, Pager pager, 
			Class<E> targetClass, String[] fieldNames) throws ModelException {
		return pageFindBySqlWithFieldNames(sql, values, pager, targetClass, fieldNames, false);
	}
	
	/**
	 * SQL查询
	 * @param sql SQL语句
	 * @param values 值集合
	 * @param pager 分页对象
	 * @param targetClass 目标类
	 * @param fieldNames 属性集合（用于显式指定属性的类型，为空时不进行设置）
	 * @param isPropChain 别名是否为属性链
	 * @return
	 * @throws ModelException
	 */
	public <E> List<E> pageFindBySqlWithFieldNames(String sql, Object[] values, Pager pager, 
			Class<E> targetClass, String[] fieldNames, boolean isPropChain) throws ModelException {
		if(targetClass == null) {
			return new ArrayList<E>();
		}
		Session session = super.getSession();
		SQLQuery sqlQuery = session.createSQLQuery(sql);
		//显式指定属性的类型
		if (fieldNames != null && fieldNames.length > 0) { 
			addScalar(sqlQuery, targetClass, fieldNames);
		}
		
		if(isPropChain) {
			//如果别名是属性链，将结果集转换为Map的集合
			sqlQuery.setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);
		} else {
			//如果别名不是属性链，直接将结果集转换为VO类型的集合
			sqlQuery.setResultTransformer(Transformers.aliasToBean(targetClass));
		}
		
		if (pager != null) {
			try {
				pager.setTotalRows(getCountBySql(sql, values));
			} catch (ModelException e) {
				throw new HibernateException("--查询记录总数失败--", e);
			}
			sqlQuery.setFirstResult(pager.getStartRow());
			sqlQuery.setMaxResults(pager.getPageSize());
		}
		setParamList(sqlQuery, values);
		
		List<E> list = new ArrayList<E>();
		if(isPropChain) {
			//如果别名是属性链，获取Map类型的结果集
			List<Map<String, Object>> mapList = sqlQuery.list();
			//将Map类型的结果集转换为VO类型的结果集
			list = SqlHelper.convertMapListToVoList(mapList, targetClass);
		} else {
			//如果别名不是属性链，获取VO类型的结果集
			list = sqlQuery.list();
		}
		for (E e : list) {
			try {
				Method method = e.getClass().getMethod("setPager", Pager.class);
				method.invoke(e, new Object[] { pager });
				break;
			}  catch (Exception ex) {
				logger.error(ex.getMessage());
			}
		}
		return list;
	}
	
	/**
	 * 获取属性的返回类型
	 * @param clazz 根类型
	 * @param properties 属性链
	 * @return
	 */
	private static Class<?> getPropRetType(Class<?> clazz, String properties){
		String[] propertyChain = properties.split("\\.");//获取属性链
		for (String pro : propertyChain) {
			String methodName = "get" + pro.substring(0, 1).toUpperCase() + pro.substring(1);
			try {
				Method method = clazz.getMethod(methodName);
				Class<?> retType = method.getReturnType();
				if(BaseModel.class.isAssignableFrom(retType)) {
					clazz = retType;
				} else {
					return retType;
				}
			} catch (Exception e) {
				throw new ModelException("不存在该方法 "+methodName);
			}
		}
		return null;
	}
	
	/**
	 * 设置VO的属性名称集到SqlQuery对象中
	 * @param sqlQuery SqlQuery对象
	 * @param clazz VO的clazz属性
	 * @param fieldNameList 属性名称集
	 */
	private static <E> void addScalar(SQLQuery sqlQuery, Class<E> clazz, String[] fieldNames) {  
        for (String fieldName : fieldNames) {
        	Class<?> returnType = getPropRetType(clazz, fieldName);
        	if(returnType != null) {
				if(byte.class.equals(returnType) || Byte.class.equals(returnType)) {
					sqlQuery.addScalar(fieldName, StandardBasicTypes.BYTE);
				} else if (short.class.equals(returnType) || Short.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.SHORT);
                } else if (int.class.equals(returnType) || Integer.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.INTEGER);
                } else if (long.class.equals(returnType) || Long.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.LONG);  
                } else if (float.class.equals(returnType) ||  Float.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.FLOAT);
                } else if (double.class.equals(returnType) || Double.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.DOUBLE);
                } else if (boolean.class.equals(returnType) || Boolean.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.BOOLEAN);
                } else if (char.class.equals(returnType) || Character.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.CHARACTER);
                } else if (String.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.STRING);
                } else if (Date.class.equals(returnType)) {  
                    sqlQuery.addScalar(fieldName, StandardBasicTypes.TIMESTAMP);
                } else if(Point.class.equals(returnType)) {
                	sqlQuery.addScalar(fieldName, GeometryUserType.TYPE);
                }
			}
        }
    }
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL查询集合(带分页)(通过别名，封装对象)，并获取传入的Pager对象
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:24:06
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @param pager 分页对象
	 * @param extraName 查询别名
	 * @param targetClass 查询后需转对象的类名
	 * @return
	 * @throws ModelException
	 */
	private Map<String, Object> pageFindBySqlAndGetPager(String sql, Object[] values, Pager pager, String extraName, Class targetClass) throws ModelException {
		Map<String, Object> mixMap = new HashMap<String, Object>();
		mixMap.put("resultList", pageFindBySql(sql, values, pager, extraName, targetClass));
		mixMap.put("pager", pager);
		return mixMap;
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 通过SQL获取行数
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:24:37
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @return
	 * @throws ModelException
	 */
	public Integer getCountBySql(String sql, Object[] values) throws ModelException {
		try {
			StringBuilder sb = new StringBuilder();
			if(sql.indexOf("group by") != -1) {
				sb.append("select count(*) from (");
				int end = sql.indexOf(" order by");
				if(end != -1) {
					sb.append(sql.substring(0, end)).append(")");
				} else {
					sb.append(sql).append(")");
				}
				sb.append(" as totalCount");
			} else {
				int begin = sql.indexOf("from");
				int end = sql.indexOf(" order by");
				sb.append("select count(*) ");
				if(end != -1) {
					sb.append(sql.substring(begin, end));
				} else {
					sb.append(sql.substring(begin));
				}
			}
			//目标SQL
			String targetSql = sb.toString();
			//System.out.println("sqlCount = "+ targetSql);
			
			Session session =super.getSession();
			Query query = session.createSQLQuery(targetSql);
			setParamList(query, values);
			
			Long beginTime = System.currentTimeMillis();
			int count = ((Number)query.uniqueResult()).intValue();
			Long endTime =System.currentTimeMillis();
			//System.out.println("总记录数查询时间="+(endTime - beginTime));
			return count;
		} catch (Exception e) {
			//e.printStackTrace();
			throw new ModelException("--------------- get count " + sql + " false", e);
		}
	}
	
	public Integer getCountBySql(String sql) throws ModelException {
	    return getCountBySql(sql, null);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 参数设置
	 * @Author:Mengfh
	 * @CreateDate:2013-6-7 下午5:24:57
	 * @param query query对象
	 * @param values 参数集合
	 */
	private void setParamList(Query query,Object[] values){
		if (values != null) {
			//参数下标
			int index = 0;
			for (int i = 0; i < values.length; i++) {
				Object obj = values[i];
				if(obj instanceof List){
					//如果参数类型为集合类型，进行循环赋值
					List params = (List)obj;
					for (Object param : params) {
						query.setParameter(index, param);
						index++;
					}
				}else{
					query.setParameter(index, obj);
					index++;
				}
			}
		}
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 添加目标对象(返回主键)
	 * @Author:Mengfh
	 * @CreateDate:2013-6-20 上午11:00:15
	 * @param t 目标对象
	 * @return 返回主键
	 * @throws ModelException
	 */
	public String addWithRet(T t) throws ModelException {
		return (String)super.getHibernateTemplate().save(t);
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 批量添加目标对象(返回主键集合)
	 * @Author:Mengfh
	 * @CreateDate:2013-6-20 上午11:01:57
	 * @param objList 目标对象集合
	 * @return 返回主键集合
	 * @throws ModelException
	 */
	public List<String> addBatchWithRet(List<T> objList) throws ModelException {
		List<String> idList = new ArrayList<String>();
		if(objList != null && !objList.isEmpty()) {
			for (T t : objList) {
				idList.add(addWithRet(t));
			}
		}
		return idList;
	}
	
	/**
	 * 
	 * @ClassName: BaseDaoBySql.java
	 * @Description: 单个查询
	 * @Author:Mengfh
	 * @CreateDate:2013-7-17 上午9:25:50
	 * @param sql SQL语句
	 * @param values 参数集合
	 * @return
	 * @throws ModelException
	 */
	public Object findObjectBySql(String sql, Object[] values) throws ModelException{
		try {
			Session session = super.getSession();
			Query query = session.createSQLQuery(sql);
			setParamList(query, values); //参数设置
			return query.uniqueResult();
		} catch (Exception e) {
			//e.printStackTrace();
			throw new ModelException("--------------- 查询单个对象失败",e);
		}
	}
	
	/**
	 * 获取SQL结果集
	 * @param sql SQL语句
	 * @param values 值集合
	 * @param clazz 目标类型
	 * @return
	 * @throws ModelException
	 */
	public <E> List<E> findBySql(String sql, Object[] values, Class<E> clazz) throws ModelException {
		Session session = super.getSession();
		SQLQuery query = session.createSQLQuery(sql);
		query.addEntity(clazz);
		setParamList(query, values); //参数设置
		return query.list();
	}
}
